Importar desde Excel 2007

[Barquichuela]

Hola amigos buen día.

Yo trabajo con Visual Fox 8 y he importado información desde Excel sin
ningún problema, el detalle que ahora tengo es que al momento de
querer importar desde Excel 2007 no me lo permite y me marca un error.

¿Conocen ustedes alguna utilería o algo que me pueda ayudar? Gracias

[Roberto Olivas]

Necesitas descargar el OLE DB provider para archivos de Office 2007, desde
el sitio de Microsoft www.microsoft.com


-----Mensaje original-----
De: mundovis...@googlegroups.com
[mailto:mundovis...@googlegroups.com] En nombre de Barquichuela
Enviado el: Martes, 10 de Febrero de 2009 09:46 a.m.
Para: Mundo Visual FoxPro
Asunto: [Mundo Visual FoxPro] Importar desde Excel 2007

[Cristobal]

lcFileXLS = ALLTRIM("miArchivo.XLS")
goExcel  = GETOBJECT(lcFileXLS)

*-- Rango de renglones
lnRenIni = 1
lnRenFin = 20

*-- Numero de Hoja
lnHoja   = 1

FOR lnRen = lnRenIni TO lnRenFin
*-- Evalua que la primer columna tenga clave, sino termina
IF ISNULL(goExcel.Sheets(lnHoja).Cells(lnRenIni, lnCveEmp).Value)
EXIT
ENDIF

lcClaveEmp = TRANSFORM( goExcel.Sheets(lnHoja).Cells(lnRen, lnCveEmp).Value)
lcClaveCol = IIF(ISNULL(goExcel.Sheets(lnHoja).Cells(lnRen, lnClave).Value), "",;
                        goExcel.Sheets(lnHoja).Cells(lnRen, lnClave).Value)
lnMonto    = IIF(ISNULL(goExcel.Sheets(lnHoja).Cells(lnRen, lnColMonto).Value), 0,;
                        goExcel.Sheets(lnHoja).Cells(lnRen, lnColMonto).Value)


SELECT curMOVS
IF !EMPTY(lcClaveEmp) AND !EMPTY(lcClaveCol) AND lnColMonto # 0
APPEND BLANK
REPLACE Emp_Id    WITH lcClaveEmp,;
        Pcp_Monto WITH lnMonto,;
        Pd_Id     WITH lcClaveCol
ENDIF
ENDFOR



Espero te ayude

[Gerardo Baron]

Roberto Olivas escribió:

esta informacion la saque de aqui

http://www.sweetpotatosoftware.com/SPSBlog/PermaLink,guid,6b9d4c6f-76bb-4444-8d5b-9e321c605534.aspx

ahi te mando el archivo zip del que hablan abajo.



Using Excel 2007 File Formats in VFP 9.0
<http://www.sweetpotatosoftware.com/SPSBlog/PermaLink,guid,6b9d4c6f-76bb-4444-8d5b-9e321c605534.aspx>

*The Problems
*Visual FoxPro 9.0's APPEND FROM and COPY TO commands are incompatible
with the new Excel 2007 file formats (XLS, XLSX, XLSB, and XLSM). You
may have noticed that I included XLS which you would think is supported,
but just try saving your Excel 97-2003 XLS workbook in compatibility
mode in Excel 2007 and then using the APPEND FROM command on it. VFP
will complain about the validity of the file - in short, it doesn't
work. And, the COPY TO command is simply stuck in the dark ages with XLS
and XL5 available. OK, so all of that is the first problem.

The second problem with the APPEND FROM and COPY TO commands is that
they don't allow you to specify the columns/fields, a where/for clause,
or specify a target range/printable region. We're pretty much stuck with
specifying a workbook. APPEND FROM does allow for an optional SHEET
parameter, but that's merely adequate not super cool.

The third problem is that the usual workaround suggested for the
problems above is to use automation, and automation is super slow due to
the COM overhead incurred. It also introduces an additional dependency
on having Excel 2007 installed on the machine on which the code is
executing. The 'slow' is never acceptable (VFP applications can be a
number of things, but slow is not one of them) and the additional
dependency, while acceptable in certain instances, is a show-stopper if
you are running these types of operations on say a web server that
doesn't have Excel 2007 installed on it.

*The Solution
*I was working on a solution for a client of mine that required that I
consume and create XLS, XLSX, XLSB, and XLSM files without the user
needing to have Excel 2007 installed. After throwing a proof-of-concept
together, I enlisted the help of my friend Bo Durban
<http://www.sweetpotatosoftware.com/SPSBlog/ct.ashx?id=6b9d4c6f-76bb-4444-8d5b-9e321c605534&url=http%3a%2f%2fblog.moxiedata.com%2f>.
The code provided in a zip at the end of this post is what we came up
with after a couple intense coding sessions. Special thanks to my client
for allowing me to share it.

*The Code*
The code includes 2 main functions: *AppendFromExcel()* and
*CopyToExcel()*. There are also 4 helper functions: AWorkSheets(),
AWorkSheetColumns(), CreateExcelTemplate(), and EmptyFieldToNull() that
you might find useful or interesting. The code:

* Does not require Office 2007
* Can append from xls, xlsx, xlsm, and xlsb file formats
* Can create and copy to xls, xlsx, xlsm, and xlsb file formats
* Supports all Excel tables (worksheets, ranges, and printable regions)
* Allows target worksheet columns and table fields to be specified
* Allows header row in worksheet to have spaces in the names of the
columns by enclosing them in standard brackets, for instance [My
Column #1]
* Provides support for SQL Where clause or VFP For clause expressions
* Is super fast
* Probably does a few other things I'm not thinking of right now :)

In any event, download/extract the prg, uncomment/modify the Sample
Usage code at the top of the prg to suit, and have fun! I orginally
included the code at the bottom of this post, but the code made this
blog entry crazy wide because of some very long lines of binary in it.
So, here's the download link for the prg file that contains the stuff
you need...

VFPExcel2007Functions.zip (19.14 KB)
<http://www.sweetpotatosoftware.com/SPSBlog/ct.ashx?id=6b9d4c6f-76bb-4444-8d5b-9e321c605534&url=http%3a%2f%2fwww.sweetpotatosoftware.com%2fSPSBlog%2fcontent%2fbinary%2fVFPExcel2007Functions.zip>

/Note: Additional refactoring is still needed and this definitely cries
out for a class to be built, but the code works fully as-is. If you have
any problems running the code (especially if a Select Data Source dialog
pops up) you might want to check the OLEDB provider comments and
alternatives provided in the code./